IF OBJECT_ID('dbo.csp_rptDepartmentalMetric_RenewalsProcessedDetails') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptDepartmentalMetric_RenewalsProcessedDetails
END
GO
/*
**************************************************************************
* Name:           csp_rptDepartmentalMetric_RenewalsProcessedDetails
* Purpose:        Get data for report
* Change History:
* Date            By          Change
* 08/26/2009       aab       Added New stored proc
**************************************************************************
*/ 
  
CREATE PROCEDURE dbo.csp_rptDepartmentalMetric_RenewalsProcessedDetails

(      
  @PeriodType VARCHAR(50)      
, @PeriodStartDate SMALLDATETIME      
 ,@Institution varchar (50) = NULL
, @Department INT = NULL    
, @Unit VARCHAR(250)  = NULL
, @PrincipalInvestigator VARCHAR(50)=NULL    
, @Sponsor  VARCHAR(150) = NULL    
, @FundNumber VARCHAR(50) = NULL
, @DomainUserId INT =  NULL
)      

AS
BEGIN

SELECT	DISTINCT 
				rp.PeriodType,
				rp.PeriodStartDate,
				ai.Institution, 
				rp.ProposalNumber, 
				ai.PrincipalInvestigator  AS PIName, 
				ai.Department,
				rp.ChiefCode,
				ai.SponsorName AS Sponsor,
				ai.SponsorAgreementId as SponsorId,
				rp.ProposalStatus,
				rp.AwardPeriod,
				rp.AwardedBy,
				rp.AwardChangeDate,
				rp.UploadDate,
				rp.AwardStatus,
				rp.PostAwardManager
FROM  rptMetric_RenewalsProcessed  rp
INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec
		ON (sec.FolderNumber = rp.ProposalNumber)
INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
		ON ai.FolderNumber = rp.ProposalNumber	
WHERE 	(ai.institution= @Institution  OR @Institution IS NULL OR @Institution = 'All')
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit=0 OR @Unit='') 
			AND  rp.PeriodStartDate = @PeriodStartDate   
			AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0')    
			AND  (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND rp.PeriodType=@PeriodType  
 

END
	
GO

